********************************************************************************
global basepath ".../Data"
global results ".../Results"
*
cap n clear all
cap n clear matrix
cap n set max_memory 400g
cap n set maxvar 120000
cap n set segmentsize 1g
cap n set max_preservemem 100g
cap n set more off
cap n set checksum off
cap n set type double
cap n set processors 6
cap n version 16.1
********************************************************************************


********************************************************************************
** Figure 1: Staggered Implementation of the U-SACCO Program
********************************************************************************

clear all
use "$basepath/dataRWA.dta", clear
*
keep if SACCO==1
gcollapse (mean) first_loan_SACCOs, by(bank_id)
gen n=1
gcollapse (sum) n, by(first_loan_SACCOs)
local new = _N + 1
set obs `new'
replace first_loan_SACCOs = ym(2008,1) in `new'
local new = _N + 1
set obs `new'
replace first_loan_SACCOs = ym(2016,12) in `new'
tsset first_loan_SACCOs
tsfill
replace n=0 if n==.
twoway (bar n first_loan_SACCOs, ///
	fc(ebblue) lc(black) barw(.75) ///
	graphregion(color(white)) bgcolor(white) ylabel(,labsize(small) angle(0)) ///
	ytitle("Number of U-SACCOs Providing the First Loan", size(small)) xlabel(#9,labsize(small) angle(0)) ///
	xtitle("Date of First U-SACCO loan", size(small)) xlabel(#10,labsize(small) angle(0)) ///
	legend(off) ///
	title("", color(black)) xsize(10))
graph save Graph "$results/Figure_1.gph", replace
graph export "$results/Figure_1.pdf", as(pdf) replace
graph export "$results/Figure_1.png", as(png) replace


********************************************************************************
** Figure 3: Commercial Banks' Branch Expansion After the U-SACCO Program
********************************************************************************

** (a) Low vs high bank presence

clear all
use "$basepath/dataRWA.dta", clear
*
gen first_loan_SACCOs_y=year(dofm(first_loan_SACCOs))
gen time_since_post=date_y-first_loan_SACCOs_y
gen low_bank_presence=(no_bank_branches_s2011/(population_by_sector/1000))
rename date_y year
gen n=1
keep district sector year time_since_post npl n low_bank_presence no_bank_branches_s2011
gcollapse (mean) time_since_post low_bank_presence no_bank_branches_s2011 (sum) npl n, by(district sector year)
merge 1:1 district sector year using "$basepath/bankbranches_by_sector_year", keep(match) nogen
ta time_since_post if low_bank_presence==0, sum(no_bank_branches)
return clear
sum low_bank_presence, detail
gen threshold=r(p75)
gen lowbankpresence=1 if low_bank_presence<=threshold
replace lowbankpresence=0 if low_bank_presence>threshold
drop threshold
encode sector, gen(sector_id)
xtset sector_id time_since_post, yearly
drop if time_since_post<0
drop if time_since_post==.
tsfill, full
sort sector_id time_since_post
by sector_id: carryforward no_bank_branches, gen(bank_branches)
by sector_id: carryforward npl, gen(NPL)
by sector_id: carryforward n, gen(nn)
drop npl n
rename NPL npl
rename nn n
drop sector
decode sector_id, gen(sector)
*
preserve
sort sector_id time_since_post
by sector_id: carryforward lowbankpresence, gen(bankpresence)
gcollapse (sum) bank_branches, by(time_since_post bankpresence)
replace time_since_post=4 if time_since_post>4
gcollapse (max) bank_branches, by(time_since_post bankpresence)
gen base=bank_branches if time_since_post==0
bysort bankpresence: ipolate base bankpresence, gen(bases)
gen G_no_bank_branches=(bank_branches/bases)*100
drop if time_since_post==. | time_since_post<0
sort bankpresence time_since_post
ta time_since_post
lab define time 0 "t=0" 1 "t=1" 2 "t=2" 3 "t=3" 4 "t>3"
lab values time_since_post time
ta time_since_post
twoway (line G_no_bank_branches time_since_post if bankpresence==1, lw(medthick) lcolor(dknavy) lp(solid) ) ///
	(line G_no_bank_branches time_since_post if bankpresence==0, lw(medthick)  lcolor(dknavy) lp(dash) ///
	graphregion(color(white)) bgcolor(white) ylabel(,labsize(small) angle(0)) ///
	ytitle("Growth No. Bank Branches", size(small)) ///
	xtitle(" " "Years Relative to Implementation", size(small)) xlabel(,valuelabel labsize(small) angle(0)) ///
	legend(order(1 2) lab( 1 "Low Bank Presence Municipalities") lab(2 "High Bank Presence Municipalities") ///
	size(small) row(1) bexpand region(lstyle(none) fcolor(none)) span) ///
	title("", color(black)) )	
graph save Graph "$results/Figure_3a.gph", replace
graph export "$results/Figure_3a.pdf", as(pdf) replace
graph export "$results/Figure_3a.png", as(png) replace   
restore

** (b) Low vs high NPLs

sort sector_id time_since_post
by sector_id: carryforward lowbankpresence, gen(bankpresence)
gegen NPL=total(npl), by(sector)
gegen N=total(n), by(sector)
gen sh_NPLs=NPL/N
drop npl n NPL N
sum sh_NPLs, detail
gen threshold=r(p75)
gen D_sh_NPLs=0 if sh_NPLs<threshold
replace D_sh_NPLs=1 if sh_NPLs>=threshold
gcollapse (sum) bank_branches, by(time_since_post D_sh_NPLs bankpresence)
replace time_since_post=4 if time_since_post>4
gcollapse (max) bank_branches, by(time_since_post D_sh_NPLs bankpresence)
gen base=bank_branches if time_since_post==0
bysort D_sh_NPLs bankpresence: ipolate base D_sh_NPLs, gen(bases)
gen G_no_bank_branches=(bank_branches/bases)*100
sort D_sh_NPLs bankpresence time_since_post
lab define time 0 "t=0" 1 "t=1" 2 "t=2" 3 "t=3" 4 "t>3"
lab values time_since_post time
ta time_since_post
twoway (line G_no_bank_branches time_since_post if D_sh_NPLs==0 & bankpresence==1, lw(medthick) lcolor(ebblue) lp(solid) ) ///
	(line G_no_bank_branches time_since_post if D_sh_NPLs==0 & bankpresence==0, lw(medthick) lcolor(ebblue) lp(dash) ) ///
	(line G_no_bank_branches time_since_post if D_sh_NPLs==1 & bankpresence==1, lw(medthick) lcolor(dknavy) lp(dash_dot ) ) ///
	(line G_no_bank_branches time_since_post if D_sh_NPLs==1 & bankpresence==0, lw(medthick) lcolor(dknavy) lp(shortdash) , ///
	graphregion(color(white)) bgcolor(white) ylabel(,labsize(small) angle(0)) ///
	ytitle("Growth No. Bank Branches", size(small)) ///
	xtitle(" " "Years Relative to Implementation", size(small)) xlabel(,valuelabel labsize(small) angle(0)) ///
	legend(order(1 2 3 4) lab( 1 "Low Bank Presence Municipalities with Low NPLs") lab(2 "High Bank Presence Municipalities with Low NPLs") ///
	lab(3 "Low Bank Presence Municipalities with High NPLs") lab(4 "High Bank Presence Municipalities with High NPLs") ///
	size(small) row(4) bexpand region(lstyle(none) fcolor(none)) span) ///
	title("", color(black)) )	
graph save Graph "$results/Figure_3b.gph", replace
graph export "$results/Figure_3b.pdf", as(pdf) replace
graph export "$results/Figure_3b.png", as(png) replace


********************************************************************************
** Figure C1: Number of Loans of U-SACCOs, Other MFIs, and Commercial Banks
********************************************************************************

clear all
use "$basepath/dataRWA.dta"
*
gen bank_type=1 if SACCO==1
replace bank_type=2 if MFI==1
replace bank_type=3 if BANK==1
gen n=1
gcollapse (sum) n, by(bank_type date_m)
gen n_SACCO=n if bank_type==1
gen n_MFI=n if bank_type==2
gen n_BANK=n if bank_type==3
gcollapse (mean) n_SACCO n_MFI n_BANK, by(date_m)
replace n_SACCO=0 if n_SACCO==.
genstack n_SACCO n_MFI n_BANK, gen(c_)
graph twoway (area c_n_BANK c_n_MFI c_n_SACCO date_m, ///
	color(gs4 gs8 gs12) graphregion(color(white)) ///
	ylabel(0(20000)120000, angle(0) labsize(small)) ytitle("Number of Loans", size(small)) ///
	xlabel(, angle(0) labsize(small)) xtitle("", size(medsmall)) graphregion(color(white)) bgcolor(white) ///
	legend(order(3 2 1) lab(1 "Commercial Banks") lab(2 "Other MFIs") lab(3 "U-SACCOs") ///
	size(small) row(1) bexpand region(lstyle(none) fcolor(none)) span) )
graph save Graph "$results/Figure_C1.gph", replace
graph export "$results/Figure_C1.pdf", as(pdf) replace
graph export "$results/Figure_C1.png", as(png) replace


********************************************************************************
** Figure C2: Credit Register Representativeness
********************************************************************************

clear all
use "$basepath/dataRWA_IndividualsAndFirms_Quarterly.dta", clear
*
keep if BANK==1
gcollapse (sum) currentbalanceamount, by(date_q bank_id_old)
rename bank_id_old bank_id
fmerge m:1 bank_id date_q using "$basepath/bank_BS.dta", keep(master match) nogen
replace gross_loans=((gross_loans)/1000000000*1000)
replace currentbalanceamount=(currentbalanceamount/1000000000*1000000)
gcollapse (sum) currentbalanceamount gross_loans, by(date_q)
twoway (line currentbalanceamount gross_loans date_q, ///
	lw(medthick medthick) lc(ebblue dknavy) lp(solid dash) ///
	graphregion(color(white)) bgcolor(white) ylabel(,labsize(small) angle(0)) ///
	ytitle("Loan Volume (in bil RWF)", size(small)) xlabel(#9,labsize(small) angle(0)) ///
	xtitle("", size(small)) xlabel(,labsize(small) angle(0)) ///
	legend(order(1 2) lab( 1 "Credit Register") lab(2 "Banks B/S (aggregate)") ///
	size(small) row(1) bexpand region(lstyle(none) fcolor(none)) span) ///
	title("", color(black)) )	
graph save Graph "$results/Figure_C2.gph", replace
graph export "$results/Figure_C2.pdf", as(pdf) replace
graph export "$results/Figure_C2.png", as(png) replace

